Database fragmentation is a condition where data is scattered in various areas of the database. Instead of being stored sequentially, the data is physically out of order and does not match the logical ordering. Fragmentation occurs when data is modified in a table. How much fragmentation occurs can depend on how often the data inserted, updated, and deleted. You need to check index fragmentation because over time, a high degree of fragmentation can cause performance issues.
If an index does not have fragmentation, the leaf level pages would be in order logically and physically, as you can see in the following diagram:
How indexes get fragmented
When you insert, update or delete data in a table, the table’s corresponding clustered indexes and the affected non-clustered indexes are modified. If the modification to an index can’t be accommodated in the same index leaf page, this can cause an index leaf page split A new leaf page will then be added that contains part of the original page and maintains the logical order of the rows in the index key. Although the new leaf page maintains the logical order of the data rows in the original page, this new page usually won’t be physically adjacent to the original page on the disk, as you can see in the diagram below. When a page is physically out of sequence, it's considered fragmented. When several pages are fragmented, running a scan across the disk is less efficient.
